package com.java.dao;


import java.sql.*;
import java.util.ArrayList;
import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import com.java.vo.ShopProductLimit;


@Repository
public class ProductLimitDao {

	private Logger logger = LoggerFactory.getLogger(getClass());

	@Autowired
	private JdbcTemplate jdbcTemplate;
	
	public int insertShopProductLimit(ShopProductLimit shopProductLimit) {
		final String sql = "INSERT INTO shop_product_limit(shop_product_id, limit_type, user_level, limit_date_type, limit_num, "
				+ " status, created_at,  updated_at)"
				+ " VALUES (?, ?, ?, ?, ?,  ?, NOW(), NOW())";
		System.out.println("插入产品sql： " + sql);
		System.out.println("插入参数： " + shopProductLimit.toString());
		KeyHolder keyHolder = new GeneratedKeyHolder();
		try{	
			jdbcTemplate.update(new PreparedStatementCreator() {
	
				@Override
				public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
					PreparedStatement ps = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
					ps.setString(1, shopProductLimit.getShopProductId());
					ps.setInt(2, shopProductLimit.getLimitType());
					ps.setLong(3, shopProductLimit.getUserLevel());
					ps.setLong(4, shopProductLimit.getLimitDateType());
					ps.setLong(5, shopProductLimit.getLimitNum());
					
					ps.setInt(6, shopProductLimit.getStatus());
					return ps;
				}
			}, keyHolder);
		}catch(Exception e){
			e.printStackTrace();
			System.out.println(e.toString());
			return -1;
		}
		return keyHolder.getKey().intValue();
	}
	
	public int UpdateShopProductLimit(ShopProductLimit shopProductLimit) {
		final String sql = "REPLACE INTO shop_product_limit(shop_product_id, limit_type, user_level, limit_date_type, limit_num, "
				+ " status, created_at,  updated_at, id)"
				+ " VALUES (?, ?, ?, ?, ?,  ?, NOW(), NOW(), ?)";
		System.out.println("更新产品sql： " + sql);
		System.out.println("更新参数： " + shopProductLimit.toString());
		KeyHolder keyHolder = new GeneratedKeyHolder();
		try{	
			return jdbcTemplate.update(new PreparedStatementCreator() {
	
				@Override
				public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
					PreparedStatement ps = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
					ps.setString(1, shopProductLimit.getShopProductId());
					ps.setInt(2, shopProductLimit.getLimitType());
					ps.setLong(3, shopProductLimit.getUserLevel());
					ps.setLong(4, shopProductLimit.getLimitDateType());
					ps.setLong(5, shopProductLimit.getLimitNum());
					
					ps.setInt(6, shopProductLimit.getStatus());
					ps.setInt(7, shopProductLimit.getId());
					return ps;
				}
			}, keyHolder);
		}catch(Exception e){
			e.printStackTrace();
			System.out.println(e.toString());
			return -1;
		}
	}
	
	public ShopProductLimit getShopProductLimitById( int id) {
		final String sql = "SELECT * FROM shop_product_limit  WHERE id = ? ";
		
		try {
			ShopProductLimit info = jdbcTemplate.queryForObject(sql, new Object[]{id}, new RowMapper<ShopProductLimit>() {

				@Override
				public ShopProductLimit mapRow(ResultSet rs, int rowNum) throws SQLException {
					ShopProductLimit info = setShopProductLimit(rs);
					return info;
				}
			});
			
			return info;
		} catch (DataAccessException e) {
			logger.info("getShopProductLimitById e = " + e.getLocalizedMessage());
		}
		
		return null;
	}
	
	private ShopProductLimit setShopProductLimit(ResultSet rs) throws SQLException{
		ShopProductLimit info = new ShopProductLimit();
		info.setId(rs.getInt("id"));
		info.setShopProductId(rs.getString("shop_product_id"));
		info.setLimitType(rs.getInt("limit_type"));
		info.setUserLevel(rs.getInt("user_level"));
		info.setLimitDateType(rs.getInt("limit_date_type"));
		info.setLimitNum(rs.getInt("limit_num"));
		info.setStatus(rs.getInt("status"));
		info.setCreatedAt(rs.getDate("created_at"));
		info.setUpdatedAt(rs.getDate("updated_at"));
		return info;
	}
	
	public List<ShopProductLimit> getShopProductByAll() {
		
		final String sql = "SELECT * FROM shop_product_limit ";
		try {
			List<ShopProductLimit> users = jdbcTemplate.query(sql, new Object[] {},
					new ResultSetExtractor<List<ShopProductLimit>>() {
						public List<ShopProductLimit> extractData(ResultSet rs)
								throws SQLException, DataAccessException {
							List<ShopProductLimit> list = new ArrayList<ShopProductLimit>();
							while (rs.next()) {
								ShopProductLimit info = setShopProductLimit(rs);
							list.add(info);
						}
						return list;
				}
			});
			return users;
		} catch (DataAccessException e) {
			logger.error("not exist ShopProductLimit by all = "  + "|" + e.getLocalizedMessage());
			return null;
		}
	}
	
	public List<ShopProductLimit> getShopProductByProductId(int productId) {
		
		final String sql = "SELECT * FROM shop_product_limit where shop_product_id = ? ";
		try {
			List<ShopProductLimit> users = jdbcTemplate.query(sql, new Object[] {productId},
					new ResultSetExtractor<List<ShopProductLimit>>() {
						public List<ShopProductLimit> extractData(ResultSet rs)
								throws SQLException, DataAccessException {
							List<ShopProductLimit> list = new ArrayList<ShopProductLimit>();
							while (rs.next()) {
								ShopProductLimit info = setShopProductLimit(rs);
							list.add(info);
						}
						return list;
				}
			});
			return users;
		} catch (DataAccessException e) {
			logger.error("not exist getShopProductByProductId  = " + "| "+productId  + "|" + e.getLocalizedMessage());
			return null;
		}
	}
	
}